import tkinter
import os
import xlrd

def jinhuo():
    rk=tkinter.Tk()    #创建一个实例对象
    rk.title("进货查询")   #标题
    rk.minsize(400,140)   #窗口大小（宽×高）

    #输入
    label0 = tkinter.Label(rk,text='表格存放路径',bg='yellow')
    label0.place(x=20,y=10,width=80,height=30)

    entry0=tkinter.Entry(rk)
    entry0.place(x=110,y=10,width=220,height=30)

    #输入
    label1 = tkinter.Label(rk,text='供应商名称',bg='yellow')
    label1.place(x=20,y=50,width=70,height=30)

    entry1=tkinter.Entry(rk)
    entry1.place(x=100,y=50,width=100,height=30)

    #输入
    label2 = tkinter.Label(rk,text='起止月',bg='yellow')
    label2.place(x=20,y=90,width=70,height=30)

    entry2=tkinter.Entry(rk)
    entry2.place(x=100,y=90,width=30,height=30)

    entry21 = tkinter.Entry(rk)
    entry21.place(x=140, y=90, width=30, height=30)
    #输入
    label3 = tkinter.Label(rk,text='起止日',bg='yellow')
    label3.place(x=20,y=130,width=70,height=30)

    entry3=tkinter.Entry(rk)
    entry3.place(x=100,y=130,width=30,height=30)

    entry4=tkinter.Entry(rk)
    entry4.place(x=140,y=130,width=30,height=30)

    #注释
    label3 = tkinter.Label(rk,text='起始日未填默认1号，\n截止日未填默认31号。',anchor='w')
    label3.place(x=200,y=130,width=120,height=40)
    
    def watch_jinhuo():
        #定义一个获取文件路径的函数
        def find_dir(filepath,gongyingshang,yue1,yue2,r1,r2):
            x = 0
            p,q = 0,0
            if r1 == '':
                r1 = '1'
            if r2 == '':
                r2 = '31'
            if eval(r1) > 9:
                start = yue1 + r1
            else:
                start = yue1 + '0' + r1
            if eval(r2) > 9:
                end = yue2 + r2
            else:
                end = yue2 + '0' + r2
            for i in os.listdir(filepath):
                gm = []
                dian = i
                path = os.path.join(filepath,i)
                if os.path.isdir(path):
                    find_dir(path,gongyingshang,yue1,yue2,r1,r2)
                if path.endswith('xlsx'):
                    #print(path)
                    wb = xlrd.open_workbook(filename=path)
                    for name in wb.sheet_names():
                        if '进货明细' in name:
                            sheet = wb.sheet_by_name(name)
                            month = sheet.col_values(1)[2:]
                            day = sheet.col_values(2)[2:]
                            gongying = sheet.col_values(3)[2:]
                            money = sheet.col_values(8)[2:]
                    for mon,d,g,m in zip(month,day,gongying,money):
                        gm.append(str(mon)+'_'+str(d)+'_'+g+'_'+str(m))
                #计算每家店的某供应商进货金额总计
                M = 0
                for i in gm:
                    if gongyingshang in i:
                        w = []      #储存'-'所在位置
                        index = -1
                        while True:
                            index = i.find('_',index+1) #从index+1位置开始找，如果找到返回索引，没找到则返回-1
                            w.append(index)
                            if index == -1:  #没找到，结束
                                break
                        if int(eval(i[w[0]+1:w[1]])) > 9 :
                            date = str(int(eval(i[0:w[0]]))) + str(int(eval(i[w[0]+1:w[1]])))
                        else:
                            date = str(int(eval(i[0:w[0]]))) + '0' + str(int(eval(i[w[0]+1:w[1]])))
                        if eval(start) <= eval(date) and eval(date) <= eval(end):
                            #print(start, date, end)
                            M += eval(i[w[2]+1:])
                    else:
                        continue
                #print(M)
                #用于显示
                labelp = tkinter.Label(rk1,text=dian,bg='white',anchor='w')
                labelp.place(x=20,y=10+40*p,width=150,height=20)
                labelq = tkinter.Label(rk1,text=str(round(M,2)),bg='white',anchor='w')
                labelq.place(x=20,y=30+40*q,width=150,height=20)
                x += M  #对所有店进行合计
                p += 1
                q += 1
            #print(x)
            #用于显示
            label_zj = tkinter.Label(rk1,text='总计='+str(round(x,2)))
            label_zj.place(x=20,y=580,width=150,height=20)
            return
        
        #获取输入路径
        filepath = entry0.get()
        gongyingshang = entry1.get()
        yue1 = entry2.get()
        yue2 = entry21.get()
        r1 = entry3.get()
        r2 = entry4.get()
        rk1=tkinter.Tk()    #创建一个实例对象
        if yue1 == yue2:
            tit = gongyingshang + yue1 + '月'
        else:
            tit = gongyingshang + yue1 + '--' + yue2 + '月'
        rk1.title(tit)   #标题
        rk1.minsize(200,620)   #窗口大小（宽×高）
        find_dir(filepath,gongyingshang,yue1,yue2,r1,r2)
        
    button_watch_jinhuo = tkinter.Button(rk, text='查看', command=watch_jinhuo)
    button_watch_jinhuo.place(x=270, y=60, width=90, height=60)
    
def chuku():
    ck=tkinter.Tk()    #创建一个实例对象
    ck.title("销售查询")   #标题
    ck.minsize(400,140)   #窗口大小（宽×高）

    #输入
    label0 = tkinter.Label(ck,text='表格存放路径',bg='yellow')
    label0.place(x=20,y=10,width=75,height=30)

    entry0=tkinter.Entry(ck)
    entry0.place(x=100,y=10,width=220,height=30)
    
    #输入
    label1 = tkinter.Label(ck,text='销售对象名称',bg='yellow')
    label1.place(x=20,y=50,width=75,height=30)

    entry1=tkinter.Entry(ck)
    entry1.place(x=100,y=50,width=100,height=30)

    #输入
    label2 = tkinter.Label(ck,text='起止月',bg='yellow')
    label2.place(x=20,y=90,width=70,height=30)

    entry2=tkinter.Entry(ck)
    entry2.place(x=100,y=90,width=30,height=30)

    entry21 = tkinter.Entry(ck)
    entry21.place(x=140, y=90, width=30, height=30)

    #输入
    label3 = tkinter.Label(ck,text='起止日',bg='yellow')
    label3.place(x=20,y=130,width=70,height=30)

    entry3=tkinter.Entry(ck)
    entry3.place(x=100,y=130,width=30,height=30)

    entry4=tkinter.Entry(ck)
    entry4.place(x=140,y=130,width=30,height=30)

    #注释
    label3 = tkinter.Label(ck,text='起始日未填默认1号，\n截止日未填默认31号。',anchor='w')
    label3.place(x=200,y=130,width=120,height=40)
    
    def watch_chuku():
        #定义一个获取文件路径的函数
        def find_dir(filepath,xiaoshouduixiang,yue1,yue2,r1,r2):
            y = 0
            p,q = 0,0
            if r1 == '':
                r1 = '1'
            if r2 == '':
                r2 = '31'
            if eval(r1) > 9:
                start = yue1 + r1
            else:
                start = yue1 + '0' + r1
            if eval(r2) > 9:
                end = yue2 + r2
            else:
                end = yue2 + '0' + r2
            for i in os.listdir(filepath):
                xm = []
                dian = i
                path = os.path.join(filepath,i)
                if os.path.isdir(path):
                    find_dir(path,xiaoshouduixiang,yue1,yue2,r1,r2)
                if path.endswith('xlsx'):
                    #print(path)
                    wb = xlrd.open_workbook(filename=path)
                    for name in wb.sheet_names():
                        if '销售明细' in name:
                            sheet = wb.sheet_by_name(name)
                            month = sheet.col_values(1)[2:]
                            day = sheet.col_values(2)[2:]
                            xiaoshou = sheet.col_values(8)[2:]
                            money = sheet.col_values(7)[2:]
                    for mon,d,x,m in zip(month,day,xiaoshou,money):
                        xm.append(str(mon)+'_'+str(d)+'_'+x+'_'+str(m))
                #计算每家店的某销售对象销售金额总计
                M = 0
                for i in xm:
                    if xiaoshouduixiang in i:
                        w = []      #储存'-'所在位置
                        index = -1
                        while True:
                            index = i.find('_',index+1) #从index+1位置开始找，如果找到返回索引，没找到则返回-1
                            w.append(index)
                            if index == -1:  #没找到，结束
                                break
                        if int(eval(i[w[0]+1:w[1]])) > 9 :
                            date = str(int(eval(i[0:w[0]]))) + str(int(eval(i[w[0]+1:w[1]])))
                        else:
                            date = str(int(eval(i[0:w[0]]))) + '0' + str(int(eval(i[w[0]+1:w[1]])))
                        if eval(start) <= eval(date) and eval(date) <= eval(end):
                            #print(start, date, end)
                            M += eval(i[w[2]+1:])
                    else:
                        continue
                #用于显示
                labelp = tkinter.Label(ck1,text=dian,bg='white',anchor='w')
                labelp.place(x=20,y=10+40*p,width=150,height=20)
                labelq = tkinter.Label(ck1,text=str(round(M,2)),bg='white',anchor='w')
                labelq.place(x=20,y=30+40*q,width=150,height=20)
                y += M  #对所有店进行合计
                p += 1
                q += 1
            #用于显示
            label_zj = tkinter.Label(ck1,text='总计='+str(round(y,2)))
            label_zj.place(x=20,y=580,width=150,height=20)
            return
        
        #获取输入路径
        filepath = entry0.get()
        xiaoshouduixiang = entry1.get()
        yue1 = entry2.get()
        yue2 = entry21.get()
        r1 = entry3.get()
        r2 = entry4.get()
        ck1=tkinter.Tk()    #创建一个实例对象
        if yue1 == yue2:
            tit = xiaoshouduixiang + yue1 +'月'
        else:
            tit = xiaoshouduixiang + yue1 + '--' + yue2 + '月'
        ck1.title(tit)   #标题
        ck1.minsize(200,620)   #窗口大小（宽×高）
        find_dir(filepath,xiaoshouduixiang,yue1,yue2,r1,r2)

    button_watch_chuku = tkinter.Button(ck, text='查看', command=watch_chuku)
    button_watch_chuku.place(x=270, y=60, width=90, height=60)

def rizhang():
    rz=tkinter.Tk()    #创建一个实例对象
    rz.title("账单查看")   #标题
    rz.minsize(350,190)   #窗口大小（宽×高）

    #输入
    label0 = tkinter.Label(rz,text='表格存放路径',bg='yellow')
    label0.place(x=20,y=10,width=75,height=30)

    entry0=tkinter.Entry(rz)
    entry0.place(x=100,y=10,width=220,height=30)
    
    #输入
    label1 = tkinter.Label(rz,text='起止月',bg='yellow')
    label1.place(x=20,y=50,width=40,height=30)

    entry1=tkinter.Entry(rz)
    entry1.place(x=70,y=50,width=30,height=30)

    entry11 = tkinter.Entry(rz)
    entry11.place(x=110, y=50, width=30, height=30)
    #输入
    label2 = tkinter.Label(rz,text='起止日',bg='yellow')
    label2.place(x=20,y=90,width=40,height=30)

    entry2=tkinter.Entry(rz)
    entry2.place(x=70,y=90,width=30,height=30)

    entry22 = tkinter.Entry(rz)
    entry22.place(x=110, y=90, width=30, height=30)
    #输入
    entry3=tkinter.Entry(rz)
    entry3.place(x=190,y=135,width=100,height=40)

    def create_rizhang():
        #定义一个获取文件路径的函数
        def find_dir(filepath,market,yue1,yue2,r1,r2):
            k1,k2 = 0,0
            global a,p
            a,p = 0,0
            names = globals()
            if eval(r1) > 9:
                start = yue1 + r1
            else:
                start = yue1 + '0' + r1
            if eval(r2) > 9:
                end = yue2 + r2
            else:
                end = yue2 + '0' + r2
            for i in os.listdir(filepath):
                gm = []
                xm = []
                dian = i
                path = os.path.join(filepath,i)
                if os.path.isdir(path):
                    find_dir(path,market,yue1,yue2,r1,r2)
                if path.endswith('xlsx') and market in dian:
                    wb = xlrd.open_workbook(filename=path)
                    for name in wb.sheet_names():
                        if '进货明细' in name:
                            sheet1 = wb.sheet_by_name(name)
                            month1 = sheet1.col_values(1)[2:]
                            day1 = sheet1.col_values(2)[2:]
                            gongying = sheet1.col_values(3)[2:]
                            money1 = sheet1.col_values(8)[2:]
                        if '销售明细' in name:
                            sheet2 = wb.sheet_by_name(name)
                            month2 = sheet2.col_values(1)[2:]
                            day2 = sheet2.col_values(2)[2:]
                            xiaoshou = sheet2.col_values(8)[2:]
                            money2 = sheet2.col_values(7)[2:]
                    for mon1,d1,g,m1 in zip(month1,day1,gongying,money1):
                        gm.append(str(mon1)+'_'+str(d1)+'_'+g+'_'+str(m1))
                    for mon2,d2,x,m2 in zip(month2,day2,xiaoshou,money2):
                        xm.append(str(mon2)+'_'+str(d2)+'_'+x+'_'+str(m2))
                    A = sorted(list(set(gongying)))
                    B = sorted(list(set(xiaoshou)))
                    if '' in A:
                        A.remove('')
                    if '' in B:
                        B.remove('')
                    #print(A,B)
                    #按店查看其中进货/销售情况
                    #进货
                    for j in A:
                        M1 = 0
                        for i in gm:
                            if j in i:
                                w = []      #储存'_'所在位置
                                index = -1
                                while True:
                                    index = i.find('_',index+1) #从index+1位置开始找，如果找到返回索引，没找到则返回-1
                                    w.append(index)
                                    if index == -1:  #没找到，结束
                                        break
                                if int(eval(i[w[0] + 1:w[1]])) > 9:
                                    date = str(int(eval(i[0:w[0]]))) + str(int(eval(i[w[0] + 1:w[1]])))
                                else:
                                    date = str(int(eval(i[0:w[0]]))) + '0' + str(int(eval(i[w[0] + 1:w[1]])))
                                if eval(start) <= eval(date) and eval(date) <= eval(end):
                                    M1 += eval(i[w[2] + 1:])
                            else:
                                continue
                        #用于显示
                        label = tkinter.Label(rz1,text=j,bg='white',anchor='w')
                        label.place(x=20,y=30+20*a,width=120,height=20)
                        label = tkinter.Label(rz1,text=str(round(M1,2)),bg='white',anchor='w')
                        label.place(x=140,y=30+20*a,width=80,height=20)
                        entr1 = tkinter.StringVar(rz1,value=str(round(M1,2)))
                        names['e{}'.format(a)] = tkinter.Entry(rz1, textvariable=entr1)
                        names['e{}'.format(a)].place(x=220, y=30 + 20 * a, width=80, height=20)
                        k1 += M1  #对所有店进行合计
                        a += 1
                    #销售
                    for j in B:
                        M2 = 0
                        for i in xm:
                            if j in i:
                                w = []      #储存'-'所在位置
                                index = -1
                                while True:
                                    index = i.find('_',index+1) #从index+1位置开始找，如果找到返回索引，没找到则返回-1
                                    w.append(index)
                                    if index == -1:  #没找到，结束
                                        break
                                if int(eval(i[w[0] + 1:w[1]])) > 9:
                                    date = str(int(eval(i[0:w[0]]))) + str(int(eval(i[w[0] + 1:w[1]])))
                                else:
                                    date = str(int(eval(i[0:w[0]]))) + '0' + str(int(eval(i[w[0] + 1:w[1]])))
                                if eval(start) <= eval(date) and eval(date) <= eval(end):
                                    M2 += eval(i[w[2] + 1:])
                            else:
                                continue
                        #用于显示
                        label = tkinter.Label(rz1,text=j,bg='white',anchor='w')
                        label.place(x=300,y=30+20*p,width=140,height=20)
                        label = tkinter.Label(rz1,text=str(round(M2,2)),bg='white',anchor='w')
                        label.place(x=440,y=30+20*p,width=80,height=20)
                        entr2 = tkinter.StringVar(rz1,value=str(round(M2, 2)))
                        names['ent{}'.format(p)] = tkinter.Entry(rz1, textvariable=entr2)
                        names['ent{}'.format(p)].place(x=520, y=30 + 20 * p, width=80, height=20)
                        k2 += M2  #对所有店进行合计
                        p += 1
                #用于显示
                label_zj1 = tkinter.Label(rz1,text='总计=')
                label_zj1.place(x=20,y=35+max(a,p)*20,width=140,height=20)
                label_zj11 = tkinter.Label(rz1, text=str(round(k1, 2)))
                label_zj11.place(x=140, y=35 + max(a,p) * 20, width=80, height=20)
                label_zj2 = tkinter.Label(rz1,text='总计=')
                label_zj2.place(x=300,y=35+max(a,p)*20,width=140,height=20)
                label_zj21 = tkinter.Label(rz1, text=str(round(k2, 2)))
                label_zj21.place(x=440, y=35 + max(a,p) * 20, width=80, height=20)

        def flush():
            count_left,count_right = 0,0
            for u in range(a):
                count_left += eval(eval('e{}.get()'.format(u)))
            label_zj12 = tkinter.Label(rz1, text=str(round(count_left, 2)))
            label_zj12.place(x=220, y=35 + max(a,p) * 20, width=80, height=20)
            for u in range(p):
                count_right += eval(eval('ent{}.get()'.format(u)))
            label_zj22 = tkinter.Label(rz1, text=str(round(count_right, 2)))
            label_zj22.place(x=520, y=35 + max(a,p) * 20, width=80, height=20)

        filepath = entry0.get()
        yue1 = entry1.get()
        yue2 = entry11.get()
        r1 = entry2.get()
        r2 = entry22.get()
        market = entry3.get()
        rz1 = tkinter.Tk()    #创建一个实例对象
        if r1 == '':
            r1 = '1'
        if r2 == '':
            r2 = '31'
        if yue1 == yue2:
            if r1 == r2:
                tit = market + yue1 + '月' + r1 + '日'
            else:
                tit = market + yue1 + '月' + r1 + '--' + r2 + '日'
        else:
            tit = market + yue1 + '月' + r1 + '日--' + yue2 + '月' + r2 + '日'
        rz1.title(tit)   #标题
        rz1.minsize(620,600)   #窗口大小（宽×高）
        label_left = tkinter.Label(rz1, text='进货', bg='white', anchor='w')
        label_left.place(x=20, y=10, width=120, height=20)
        label_left = tkinter.Label(rz1, text='表格数据统计', bg='white', anchor='w')
        label_left.place(x=140, y=10, width=80, height=20)
        label_left = tkinter.Label(rz1, text='自填修正数据', bg='white', anchor='w')
        label_left.place(x=220, y=10, width=80, height=20)
        label_right = tkinter.Label(rz1, text='销售', bg='white', anchor='w')
        label_right.place(x=300, y=10, width=120, height=20)
        label_right = tkinter.Label(rz1, text='表格数据统计', bg='white', anchor='w')
        label_right.place(x=440, y=10, width=80, height=20)
        label_right = tkinter.Label(rz1, text='自填修正数据', bg='white', anchor='w')
        label_right.place(x=520, y=10, width=80, height=20)
        find_dir(filepath,market,yue1,yue2,r1,r2)
        button_flush = tkinter.Button(rz1, text='刷新自填数据总计', command=flush)
        button_flush.place(x=200, y=55 + max(a, p) * 20, width=150, height=20)
    button_watch_rizhang = tkinter.Button(rz, text='按店查看进货销售账单', command=create_rizhang)
    button_watch_rizhang.place(x=20, y=135, width=150, height=40)
if __name__ == '__main__':
    root=tkinter.Tk()    #创建一个实例对象
    root.title("查账")   #标题
    root.minsize(360,180)   #窗口大小（宽×高）

    button_ruku = tkinter.Button(root, text='进货查询', command=jinhuo)
    button_ruku.place(x=70, y=20, width=90, height=60)
    button_chuku = tkinter.Button(root, text='销售查询', command=chuku)
    button_chuku.place(x=200, y=20, width=90, height=60)
    button_rizhang = tkinter.Button(root, text='店铺账单', command=rizhang)
    button_rizhang.place(x=70, y=100, width=90, height=60)

    root.mainloop()